# -*- coding: utf-8 -*-
"""
Nome do módulo: bd.py
autores: Pedro Caixinha Nº4437 e Pedro Lopes Nº9850
data: 30/10/2013
Obs.: Este módulo mapea uma estrutura de base de dados para o programa através do ORM SQLAlchemy.
      Contém funções que permitem popular a base de dados com os dados do ficheiro "cna131fresultados.xls".
"""
import xlrd
import util
import os

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

class Instituicao(Base):
    """
    Mapea a tabela Instituições da base de dados.
    """ 
    __tablename__ = u'Instituicoes'

    id = Column(Integer, primary_key = True)
    nome = Column(String)
    distrito = Column(String)

    def __init__(self, nome, distrito):
        """
        Construtor que inicializa a classe.

            Argumentos:
                nome -- Coluna da tabela com o nome "nome".
                distrito -- Coluna da tabela com o nome "distrito".
        """
        self.nome = nome
        self.distrito = distrito
        
class Escola(Base):
    """
    Mapea a tabela Escolas da base de dados.
    """ 
    __tablename__ = 'Escolas'

    id = Column(Integer, primary_key = True)
    cod = Column(Integer)
    nome = Column(String)
    id_instituicao = Column(Integer, ForeignKey(u'Instituicoes.id'))

    def __init__(self, cod, nome, id_instituicao):
        """
        Construtor que inicializa a classe.

            Argumentos:
                cod -- Coluna da tabela com o nome "cod".
                nome -- Coluna da tabela com o nome "nome".
                distrito -- Coluna da tabela com o nome "distrito".
        """
        self.cod = cod
        self.nome = nome
        self.id_instituicao = id_instituicao

class Curso(Base):
    """
    Mapea a tabela Cursos da base de dados.
    """ 
    __tablename__ = 'Cursos'

    id = Column(Integer, primary_key = True)
    cod = Column(Integer)
    nome = Column(String)

    def __init__(self, cod, nome):
        """
        Construtor que inicializa a classe.

            Argumentos:
                cod -- Coluna da tabela com o nome "cod".
                nome -- Coluna da tabela com o nome "nome".
        """
        self.cod = cod
        self.nome = nome

class EscolaCurso(Base):
    """
    Mapea a tabela Escola_Curso da base de dados.
    """ 
    __tablename__ = 'Escola_Curso'

    id = Column(Integer, primary_key = True)
    grau = Column(String)
    vagas_iniciais = Column(Integer)
    n_colocados = Column(Integer)
    nota_cuccg = Column(Integer)
    vagas_sobrantes = Column(Integer)
    id_escola = Column(Integer, ForeignKey('Escolas.id'))
    id_curso = Column(Integer, ForeignKey('Cursos.id'))

    def __init__(self, grau, vagas_iniciais, n_colocados, nota_cuccg, vagas_sobrantes, id_escola, id_curso):
        """
        Construtor que inicializa a classe.

            Argumentos:
                grau -- Coluna da tabela com o nome "grau".
                vagas_iniciais -- Coluna da tabela com o nome "vagas_iniciais".
                n_colocados -- Coluna da tabela com o nome "n_colocados".
                nota_cuccg -- Coluna da tabela com o nome "nota_cuccg".
                vagas_sobrantes -- Coluna da tabela com o nome "vagas_sobrantes".
                id_escola -- Coluna da tabela com o nome "id_escola".
                id_curso -- Coluna da tabela com o nome "id_curso".
        """
        self.grau = grau
        self.vagas_iniciais = vagas_iniciais
        self.n_colocados = n_colocados
        self.nota_cuccg = nota_cuccg
        self.vagas_sobrantes = vagas_sobrantes
        self.id_escola = id_escola
        self.id_curso = id_curso

class BaseDados:
    """
    Classe BaseDados.
    """
    def criar_bd(self):
        """
        Função que cria a base de dados.

            return -- Devolve o engine (a base de dados).
        """

        base_dados_path = os.path.exists(os.getcwd() + '/base_dados.db')
       
        if base_dados_path == True:
            os.remove('base_dados.db')
            base_dados = create_engine('sqlite:///base_dados.db')
            Base.metadata.create_all(base_dados)
            Session = sessionmaker(bind=base_dados)
            session = Session()
            self.inserir_instituicoes(session)
            self.inserir_escolas(session)
            self.inserir_cursos(session)
            self.inserir_escola_curso(session)

            return base_dados

        else:
            base_dados = create_engine('sqlite:///base_dados.db')
            Base.metadata.create_all(base_dados)                  
            Session = sessionmaker(bind=base_dados)
            session = Session()
            self.inserir_instituicoes(session)
            self.inserir_escolas(session)
            self.inserir_cursos(session)
            self.inserir_escola_curso(session)

            return base_dados        

    def inserir_instituicoes(self, session):

        """
        Função que obtém, processa, e armazena na tabela "Instituicao" da base de dados a informação respeitante às instituições de ensino superior.
        """
        
        livro = xlrd.open_workbook("cna131fresultados.xls")
        pag = livro.sheet_by_index(0)
        coluna_leitura = 2
       
        dic = {u"Universidade dos Açores":u"Região Autónoma dos Açores", u"Instituto Politécnico de Santarém":u"Santarém", u"Escola Superior de Enfermagem de Coimbra":u"Coimbra", u"Escola Superior de Enfermagem de Lisboa":u"Lisboa", u"Universidade do Porto":u"Porto", u"Instituto Politécnico de Tomar":u"Santarém", u"Universidade de Évora":u"Évora", u"Instituto Politécnico de Coimbra":u"Coimbra", u"Universidade do Minho":u"Braga", u"Universidade Nova de Lisboa":u"Lisboa", u"Instituto Politécnico de Castelo Branco":u"Castelo Branco", u"Instituto Politécnico do Porto":u"Porto", u"Universidade Técnica de Lisboa":u"Lisboa", u"Instituto Politécnico de Portalegre":u"Portalegre", u"Escola Superior de Hotelaria e Turismo do Estoril":u"Lisboa", u"Instituto Politécnico de Beja":u"Beja", u"Universidade de Coimbra":u"Coimbra", u"Instituto Politécnico de Lisboa":u"Lisboa", u"Instituto Politécnico de Bragança":u"Bragança", u"Universidade de Aveiro":u"Aveiro", u"ISCTE":u"Lisboa", u"Universidade da Beira Interior":u"Castelo Branco", u"Universidade de Lisboa":u"Lisboa", u"Escola Superior de Enfermagem do Porto":u"Porto", u"Instituto Politécnico de Viana do Castelo":u"Viana do Castelo", u"Instituto Politécnico de Viseu":u"Viseu", u"Instituto Politécnico da Guarda":u"Guarda", u"Instituto Politécnico do Cávado e do Ave":u"Braga", u"Universidade do Algarve":u"Faro", u"Escola Superior Náutica Infante D. Henrique":u"Lisboa", u"Instituto Politécnico de Setúbal":u"Setúbal", u"Instituto Politécnico de Leiria":u"Leiria", u"Universidade da Madeira":u"Região Autónoma da Madeira", u"Universidade de Trás-os-Montes e Alto Douro":u"Vila Real"}

        lista_instituicoes = []
        lista_final = [] 
    
        for linha in range(pag.nrows):
            dados = pag.cell_value(linha, coluna_leitura)
            lista_instituicoes.append(util.separa_dados(dados)[0])

        lista_instituicoes = filter(lambda x: True if not x == "(3)" else False, lista_instituicoes)
        lista_instituicoes = filter(lambda x: True if not x == u"Nome da instituição" else False, lista_instituicoes)
        lista_instituicoes = filter(lambda x: True if not x == u"" else False, lista_instituicoes)

        lista_instituicoes = [x for x in set(lista_instituicoes)]

        for instituicao in lista_instituicoes:
            for chave, valor in dic.iteritems():
                if (chave == instituicao):
                    lista_final.append((instituicao, valor))

        for instituicao, distrito in lista_final:
            dados = Instituicao(instituicao, distrito)
            session.add(dados)

        session.commit()
        session.close()

        return 0 

    def inserir_escolas(self, session):
     
        """
        Função que obtém, processa, e armazena na tabela "Escolas" da base de dados a informação respeitante às diferentes escolas existentes nas instituições de ensino superior.
        """
        
        livro = xlrd.open_workbook("cna131fresultados.xls")
        pag = livro.sheet_by_index(0)
       
        coluna_leitura1 = 0
        coluna_leitura2 = 2
        lista = []
        lista_escolas = []
        lista_final = []

        for linha in range(pag.nrows):
            dados1 = pag.cell_value(linha, coluna_leitura1)
            dados2 = pag.cell_value(linha, coluna_leitura2)
            lista.append((dados1, dados2))

        lista = [x for x in set(lista)]
        lista = filter(lambda x: True if not x == (u"CONCURSO NACIONAL DE ACESSO <AO ENSINO SUPERIOR PÚBLICO DE 2013: 1.ª FASE","") else False, lista)
        lista = filter(lambda x: True if not x == (u"(1)",u"(3)") else False, lista)
        lista = filter(lambda x: True if not x == ("","") else False, lista)
        lista = filter(lambda x: True if not x == (u"Código da instituição",u"Nome da instituição") else False, lista)

        for (codigo, escola) in lista:
            dados = util.separa_dados(escola)
            if len(dados) == 2:
                lista_escolas.append((codigo, dados[0], dados[1]))
            else:
                lista_escolas.append((codigo, dados[0], dados[0]))

        for codigo, instituicao, escola in lista_escolas:
            q_res = session.query(Instituicao).filter(Instituicao.nome == instituicao)
            for instancia in q_res:
                lista_final.append((codigo, escola, instancia.id))

        for codigo, escola, id_instituicao in lista_final:
            dados = Escola(codigo, escola, id_instituicao)
            session.add(dados)

        session.commit()
        session.close()
        return 0

    def inserir_cursos(self, session):

        """
        Função que obtém, processa, e armazena na tabela "Cursos" da base de dados a informação respeitante aos diferentes cursos.
        """
        
        livro = xlrd.open_workbook("cna131fresultados.xls")
        pag = livro.sheet_by_index(0)
       
        coluna_leitura1 = 1
        coluna_leitura2 = 3
        lista_cursos = []

        for linha in range(pag.nrows):
            dados1 = pag.cell_value(linha, coluna_leitura1)
            dados2 = pag.cell_value(linha, coluna_leitura2)
            lista_cursos.append((dados1,dados2))

        lista_cursos = filter(lambda x: True if not x == ("","") else False, lista_cursos)
        lista_cursos = filter(lambda x: True if not x == ("",u"TOTAL") else False, lista_cursos)
        lista_cursos = filter(lambda x: True if not x == (u"Código do curso",u"Nome do curso") else False, lista_cursos)
        lista_cursos = filter(lambda x: True if not x == (u"(2)",u"(4)") else False, lista_cursos)
        lista_cursos = [x for x in set(lista_cursos)]

        for codigo, curso in lista_cursos:
            dados = Curso(codigo, curso)
            session.add(dados)

        session.commit()
        session.close()

        return 0

    def inserir_escola_curso(self, session):

        """
        Função que obtém, processa, e armazena na tabela "Escola_Curso" da base de dados os dados da folha de cálculo relativos a:
        - Grau dos cursos;
        - Nº de vagas iniciais de cada curso;
        - Nº de colocados em cada curso;
        - Nota de candidatura do ultimo colocado pelo contingente geral em cada curso;
        - Nº de vagas sobrantes em cada curso.
        - Id da Escola ao qual pertence o curso (Foreign Key).
        - Id do Curso (Foreign Key).
        """

        livro = xlrd.open_workbook("cna131fresultados.xls")
        pag = livro.sheet_by_index(0)
    
        coluna_leitura1 = 0
        coluna_leitura2 = 1
        coluna_leitura3 = 4
        coluna_leitura4 = 5
        coluna_leitura5 = 6
        coluna_leitura6 = 7
        coluna_leitura7 = 8

        lista = []
        lista_final = []

        for linha in range(pag.nrows):
            dados1 = pag.cell_value(linha, coluna_leitura1)
            dados2 = pag.cell_value(linha, coluna_leitura2)
            dados3 = pag.cell_value(linha, coluna_leitura3)
            dados4 = pag.cell_value(linha, coluna_leitura4)
            dados5 = pag.cell_value(linha, coluna_leitura5)
            dados6 = pag.cell_value(linha, coluna_leitura6)
            dados7 = pag.cell_value(linha, coluna_leitura7)
            
            lista.append((dados1, dados2, dados3, dados4, dados5, dados6, dados7))

        lista = filter(lambda x: True if not x == ("","","","","","","") else False, lista)
        lista = filter(lambda x: True if not x == (u"CONCURSO NACIONAL DE ACESSO AO ENSINO SUPERIOR PÚBLICO DE 2013: 1.ª FASE","","","","","","") else False, lista)
        lista = filter(lambda x: True if not x == (u"Código da instituição",u"Código do curso",u"Grau",u"Vagas iniciais",u"Colocados ",u"Nota de candidatura do último colocado pelo contingente geral",u"Vagas sobrantes") else False, lista)
        lista = filter(lambda x: True if not x == (u"(1)",u"(2)",u"(5)",u"(6)",u"(7)",u"(8)",u"(9)") else False, lista)
        lista = filter(lambda x: True if not x == ("","","",51461.0,37415.0,"",14176.0) else False, lista)
        
        for cod_escola, cod_curso, grau, vagas_iniciais, n_colocados, nota_cuccg, vagas_sobrantes in lista:
            q_res1 = session.query(Escola).filter(Escola.cod == cod_escola)
            q_res2 = session.query(Curso).filter(Curso.cod == cod_curso)
            for instancia1, instancia2 in zip(q_res1, q_res2):
                lista_final.append((grau, vagas_iniciais, n_colocados, nota_cuccg, vagas_sobrantes, instancia1.id, instancia2.id))
        
        for grau, vagas_iniciais, n_colocados, nota_cuccg, vagas_sobrantes, id_escola, id_curso in lista_final:
            dados = EscolaCurso(grau, vagas_iniciais, n_colocados, nota_cuccg, vagas_sobrantes, id_escola, id_curso)
            session.add(dados)

        session.commit()
        session.close()

        return 0

def main():

    """Teste básico do módulo"""

    obj_bd = BaseDados()
    bd = obj_bd.criar_bd()
   
if __name__ == "__main__": main()








